Left join to same table twice

jblayney
jblayney used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
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..

Author

Commented:
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

Author

Commented:
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?
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Author

Commented:
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

Author

Commented:
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
Senior Developer
Commented:
When you want to get the column data per column name, then you need unique names in the SELECT list, e.g.

SELECT	TA.artist_name AS TA_artist_name,
		IPA.artist_name AS IPA_artist_name
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 

Open in new window

With $row_Recordset_get_deposit['TA_artist_name'] and $row_Recordset_get_deposit['IPA_artist_name'].

Author

Commented:
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 WoodsIT Guru
Most Valuable Expert 2011

Commented:
You can include table_name.* for all the columns from table_name.
ste5anSenior Developer

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial