Complex phpMysql Query with table output

[2tonejoe]

Ok. I am no pro, but I can handle my own in simple query output. My issue arose when trying to do a query across two tables with some comparisons being made. . . .

 

What I need:

---------------

I need to query a table (table-a) that has the following information:

id code title ver1 ver2 ver3 ver4 ver5 ver6 ver7 ver8 ver9ver10 Area adsAvail last updated

5 FAA Fabianna Austin All THUV NJIT DUNI Boston 4 2007-12-06 11:30:50

6 FBA Fabianna Boston All Boston 4 2007-11-29 16:07:16

 

Ok. There is another table (table-b) that holds ad information. Basically, they enter information into a web form for positions 1-x in adsAvail for each verX for each code.

id code ver week year booked position last updated

21 ABA PINE 1 1947 1 2 2007-11-14 16:44:34

What I need is a report that shows each position in 1-x (x from table-a) that is not "booked" for every ver for every code in table-a. make sense?? I know what I need, data wise, but it is baffling me on how to accomplish this. . . . .

 

 

[craygo]

try this

 

$sql = "SELECT * from table-a 
LEFT JOIN table-b as vers1 ON table-a.ver1 = vers1.code
LEFT JOIN table-b AS vers2 ON table-a.ver2 = vers2.code
LEFT JOIN table-b AS vers3 ON table-a.ver2 = vers3.code
LEFT JOIN table-b AS vers4 ON table-a.ver2 = vers4.code
LEFT JOIN table-b AS vers5 ON table-a.ver2 = vers5.code
LEFT JOIN table-b AS vers6 ON table-a.ver2 = vers6.code
LEFT JOIN table-b AS vers7 ON table-a.ver2 = vers7.code
LEFT JOIN table-b AS vers8 ON table-a.ver2 = vers8.code
LEFT JOIN table-b AS vers9 ON table-a.ver2 = vers9.code
LEFT JOIN table-b AS vers10 ON table-a.ver2 = ver102.code
WHERE booked != '1'";

 

See what you can do with that

 

May want to add some grouping or order by

 

Ray

[2tonejoe]

mysql> select * from comp_book_info LEFT JOIN cover_booking as vers1 ON comp_book_info.ver1 = vers1.book_code LEFT JOIN cover_booking AS vers2 ON comp_book_info.ver2 = vers2.book_code LEFT JOIN cover_booking AS vers3 ON comp_book_info.ver2 = vers3.book_code LEFT JOIN cover_booking AS vers4 ON comp_book_info.ver2 = vers4.book_code LEFT JOIN cover_booking AS vers5 ON comp_book_info.ver2 = vers5.book_code LEFT JOIN cover_booking AS vers6 ON comp_book_info.ver2 = vers6.book_code LEFT JOIN cover_booking AS vers7 ON comp_book_info.ver2 = vers7.book_code LEFT JOIN cover_booking AS vers8 ON comp_book_info.ver2 = vers8.book_code LEFT JOIN cover_booking AS vers9 ON comp_book_info.ver2 = vers9.book_code LEFT JOIN cover_booking AS vers10 ON comp_book_info.ver2 = ver102.book_code WHERE cover_booking.booked != '1';

Unknown column 'cover_booking.booked' in 'where clause'

cover_booking.booked is a valid table.column though . . .?

[2tonejoe]

= bump =

 

can someone move my thread to the Mysql Help forum? I think I will get better coverage there thanks

[craygo]

Can't use cover_booking cause you gave the table an alias. each cover_booking got a new name so it will be vers1.booked.

 

you may have to put in a WHERE for each link

 

vers1.booked !=1 AND

vers2.booked != 1...

 

and so on

 

Ray