Solved

sql select query joining multiple tables with foreign keys

Posted on 2011-09-25
5
459 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:h3rm1t9536
  • 3
5 Comments
 
LVL 15

Expert Comment

by:Eyal
ID: 36594928
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

0
 
LVL 2

Expert Comment

by:Lord_Death
ID: 36594940
declare @WorkGroupID decimal = 0;
SELECT   *
From  ....
WHERE   (@WorkGroupID = 0 OR OwnerWorkGroupID = @WorkGroupID) AND ...
0
 

Author Comment

by:h3rm1t9536
ID: 36594994
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!
0
 
LVL 15

Accepted Solution

by:
Eyal earned 250 total points
ID: 36594998
if you pass the parameter $country as NULL so the sql will see it like so... NULL is NULL
that means true and all rows will be returned
if the $country will not be null then this condision will be ignored and country = $country will be applied
0
 
LVL 15

Expert Comment

by:Eyal
ID: 36594999
and regarding the print $row['InsertStockValue'] will do
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
maybe no no httpd.conf 6 47
mod_rewrite # anchor problem. 6 17
SQL Server 2012 r2 - Varible Table 3 24
SQL Improvement  ( Speed) 14 28
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question