asked on

Left join to same table twice

Hello,

I have to Left join to the same 2 tabs twice, I know you are supposed to us an alias AS, but I can't get the syntax correct.

Here is my base query:
I am joining the the artist table and client table from both the tps_tour and invoice_project tables.

SELECT * FROM daily_deposit 

LEFT JOIN deposit_venue ON daily_deposit.daily_deposit_id = deposit_venue.deposit_venue_deposit 
LEFT JOIN show_settlement ON deposit_venue.deposit_venue_settlements = show_settlement.show_settlement_id 
LEFT JOIN tps_shows ON show_settlement.show_settlement_show = tps_shows.tps_shows_id 

LEFT JOIN tps_tour ON tps_shows.tps_shows_tps = tps_tour.tps_tour_id 
	LEFT JOIN artist ON tps_tour.tps_tour_artist = artist.artist_id 
	LEFT JOIN client ON tps_tour.tps_tour_client = client.client_id 

 
LEFT JOIN invoice_project ON deposit_invoice.deposit_invoice_invoice = invoice_project.invoice_project_id  
	LEFT JOIN artist ON invoice_project.invoice_project_artist = artist.artist_id 
	LEFT JOIN client ON invoice_project.invoice_project_client = client.client_id 


 $criteria  GROUP BY daily_deposit_id  ORDER BY $final_sort  LIMIT $startnumber, $number_per_pag

Open in new window

ste5an

E.g.

SELECT  *
FROM    daily_deposit DD
    LEFT JOIN deposit_venue DV ON DD.daily_deposit_id = DV.deposit_venue_deposit
    LEFT JOIN show_settlement SS ON DV.deposit_venue_settlements = SS.show_settlement_id
    LEFT JOIN tps_shows TS ON SS.show_settlement_show = TS.tps_shows_id
    LEFT JOIN tps_tour TT ON TS.tps_shows_tps = TT.tps_tour_id
    LEFT JOIN artist TA ON TT.tps_tour_artist = TA.artist_id
    LEFT JOIN client TC ON TT.tps_tour_client = TC.client_id
    LEFT JOIN invoice_project IP ON deposit_invoice.deposit_invoice_invoice = IP.invoice_project_id
    LEFT JOIN artist IA ON IP.invoice_project_artist = IA.artist_id
    LEFT JOIN client IC ON IP.invoice_project_client = IC.client_id

Open in new window

But you're using a table in line 9 in the JOIN condition, which is not in your FROM or JOINs..

jblayney

ASKER

thank you, I had a few more joins that I removed, I was trying to make the post cleaner and just have the relevant ones, let me give this a test and get back to you

jblayney

ASKER

and just to clarify,

1. we don't need to use AS anymore?
2. once you assign an alias to one table you need to assign to all of them?

jblayney

ASKER

Hello,

Im not getting any errors, but later on in the page when I echo my results, only the invoice_project artists and clients are showing, this is my complete query

I should mention both are in the same list, and it will be one or the other, either an IP artist or a TT artist

SELECT * FROM daily_deposit DD 


LEFT JOIN deposit_type DT ON DD.daily_deposit_type = DT.deposit_type_id 
LEFT JOIN deposit_invoice DI ON DD.daily_deposit_id = DI.deposit_invoice_deposit 
LEFT JOIN deposit_venue DV ON DD.daily_deposit_id = DV.deposit_venue_deposit 
LEFT JOIN show_settlement SS ON DV.deposit_venue_settlements = SS.show_settlement_id 
LEFT JOIN tps_shows TS ON SS.show_settlement_show = TS.tps_shows_id 

LEFT JOIN tps_tour TT ON TS.tps_shows_tps = TT.tps_tour_id 
LEFT JOIN venue VEN ON  TS.tps_shows_venue = VEN.venue_id 

	LEFT JOIN artist TA ON TT.tps_tour_artist = TA.artist_id 
	LEFT JOIN client TC ON TT.tps_tour_client = TC.client_id 

 
LEFT JOIN invoice_project IP ON DI.deposit_invoice_invoice = IP.invoice_project_id  
	LEFT JOIN artist IPA ON IP.invoice_project_artist = IPA.artist_id 
	LEFT JOIN client IPC ON IP.invoice_project_client = IPC.client_id 


 $criteria  GROUP BY DD.daily_deposit_id  ORDER BY $final_sort  LIMIT $startnumber, $number_per_page";



$row_Recordset_get_deposit['artist_name']

Open in new window

jblayney

ASKER

Interesting fact, if I switch the order of there inner joins in the query and have the tps_tour second, then those artists display and the invoice project artist don't

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jblayney

ASKER

Hello,
I understand,, but once I start selecting specific columns, do I then need to call every single column from every table that I am using? or is there a way we can add an * to grab the rest of the columns?

Terry Woods

You can include table_name.* for all the columns from table_name.

ste5an

While you can use the asterisk, it is bad coding style. In SQL you use always explicit column lists, if possible.

jblayney

ASKER

thank you