asked on

sql select query joining multiple tables with foreign keys

Hi There!

Hoping if someone would be able to help me out?

I am trying to write a select query that joins all tables I have got the following code:

SELECT 
			DATE_FORMAT(OrdersDate, '%M %d, %Y'), 
			LocalOrderNumberValue, 
			OrdersName, 
			OrdersQuantity, 
			InsertSizeValue, 
			InsertStockValue, 
			CoverStockFinishValue, 
			CoverFormatSizeValue, 
			Content, 
			StrategicApplicationValue, 
			DistributionChannelValue, 
			DistributionChannelDetailsValue, 
			AccountValue, 
			Country, 
			IndustrySectorValue, 
			IndustrySubSectorValue
		FROM 
			account,
			content
			country,
			cover_format_size,
			cover_stock_finish,
			distribution_channel,
			distribution_channel_details,
			industry_sector,
			industry_sub_sector,
			insert_size,
			insert_stock,
			local_order_number,
			orders,
			orders_bridge_account,
			strategic_application
		WHERE 
			orders.AccountID = orders_bridge_account.AccountID
			AND orders_bridge_account.OrdersID = orders.OrdersID
			AND orders_bridge_account.AccountID = account.AccountID
			AND orders.ContentID = content.ContentID
			AND orders.CountryID = country.CountryID
			AND orders.CoverFormatSizeID = cover_format_size.CoverFormatSizeID
			AND orders.CoverStockFinishID = cover_stock_finish.CoverStockFinishID
			AND orders.DistributionChannelID = distribution_channel.DistributionChannelID
			AND orders.DistributionChannelDetailsID = distribution_channel_details.DistributionChannelDetailsID
			AND orders.IndustrySectorID = industry_sector.IndustrySectorID
			AND orders.IndustrySubSectorID = industry_sub_sector.IndustrySubSectorID
			AND orders.InsertSizeID = insert_size.InsertSizeID
			AND orders.InsertStockID = insert_stock.InsertStockID
			AND orders.LocalOrderNumberID = local_order_number.LocalOrderNumberID
			AND orders.StrategicApplicationID = strategic_application.StrategicApplicationID
		ORDER BY 
			$order_by 
		LIMIT 
			$start, $display";

Open in new window

I would however like to add to that code a variable that would allow for the results to be refinded. eg. If the user selects country to be equal to South Africa...

Also another example ... if the user would like the date to be between $date1 and $date2 and for the country to be equal to South Africa...

What should I add to my sql query? Would I be able to just add lines of code to the WHERE part... for example:

SELECT
...
FROM
...
WHERE 
			orders.AccountID = orders_bridge_account.AccountID
			AND orders_bridge_account.OrdersID = orders.OrdersID
			AND orders_bridge_account.AccountID = account.AccountID
AND country = $country
AND date = $date
...

Open in new window

What way would be best??

Also just hoping for a little insight into indexes - what index should I make for a statement like this? I'm new to SQL and databases and would be great if I could get a little help !

Thanks so much in advance!!
Chelsesa

Eyal

SELECT
...
FROM
...
WHERE 
			orders.AccountID = orders_bridge_account.AccountID
			AND orders_bridge_account.OrdersID = orders.OrdersID
			AND orders_bridge_account.AccountID = account.AccountID
AND (country = $country OR $country is null)
AND (date = $date or $date is null)
...

Open in new window

Lord_Death

declare @WorkGroupID decimal = 0;
SELECT *
From ....
WHERE (@WorkGroupID = 0 OR OwnerWorkGroupID = @WorkGroupID) AND ...

h3rm1t9536

ASKER

Hi Eval

Sorry what does it mean when you add '...OR $country is null' to the query? Does it mean select all rows where country is equal to South Africa and where country is equal to NULL?

Sorry and a quick question when I fetch the records and try print them would the code be something like this....
$row['insert_stock.InsertStockValue']

or would it be like this
$row['InsertStockValue']?

Thanks for your help!

ASKER CERTIFIED SOLUTION

Eyal

membership

This solution is only available to members.

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

Start Free Trial

Eyal

and regarding the print $row['InsertStockValue'] will do