have tried joining several tables and the result displays duplicate rows of virtually every line/row. i have tried using distinct but failed miserably.
select purchaseorders.traderid,
suppliers.name
stockbatches.partid,
allpartmaster.partdesc,
allpartmaster.prodgroup,
[code]....
I am still fairly new to SQL, having been tasked with creating a csv file from data now someone else has left.
I can do the csv export using sqlcmd and I have the query sorted and am pulling out the right data, but it generates two rows, as one of the tables has multiple records per cardholder. See the query below, I know there is a way of doing it with XML PATH, I think, but it has got me slightly confused.
set nocount on
selectdbo.card.EncodedNumber,
dbo.card.IsEnabled,
dbo.Cardholder.FirstName,
dbo.cardholder.LastName,
dbo.card.ExpiryTime,
dbo.PersonalDataString.Value
[Code] .....
I have a temp table with the following columns and data
drop table #temp
create table #temp (id int,DLR_ID int,KPI_ID int,Brnd_ID int)
insert into #temp values (1,2343,34,2)
insert into #temp values (2,2343,34,2)
insert into #temp values (3,2343,34,2)
[Code]....
I use the rank function on that table and get the following results
select rank() over (order by DLR_ID,KPI_ID,BRND_ID ) Rown,* from #temp
I am interested only in Rown and Id columns. For each Rown number, I need to get the min(ID) in the second column and the duplicate ID should be in 3rd column as shown below.If i have 3 duplicate IDs , I should have 3 rows with 2nd column being the min(id) and 3rd column having one of the duplicate ids in ascending order(as shown in Rown=6)
We have the below query which is pulling in Sales and Revenue information. Since the sale is recorded in just one month and the revenue is recorded each month, we need to have the results of this query to only list the Sales amount once, but still have all the other revenue amounts listed for each month. In this example, the sale is record in year 2014 and month 10, but there are revenues in every month as well for the rest of 2014 and the start of 2015 but we only want to the sales amount to appear once on this results set.
SELECT
project.project_number,
project.country_code,
project.project_desc,
gsl.global_service_line_desc,
buy.buyer_desc,
[Code] ....
Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!
View 4 Replies View RelatedHi All,
Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.
My SQL statment is as follows :-
SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx
I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.
Let me start off with my table layouts.
CREATE TABLE [dbo].[Competition] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (150) NOT NULL,
[Remarks] NVARCHAR (350) NULL,
[Start] DATETIME NOT NULL,
[IsActive] BIT NULL,
[End] DATETIME NOT NULL,
[Code] ....
Then here is my query with the problem below. The problem is that multiple EntryImages can relate to a single Entry. My goal is to only select one Row for each Entry (using @EntrySelection) but when I join to EntryImages I always get back multiple rows when an Entry has multiple EntryImages.
Declare@EntrySelection int= 10
select top (@EntrySelection)
[dbo].Entries.Id,
[dbo].Entries.CatchDate,
[dbo].Entries.CompetitionId,
[dbo].Competition.Name,
[Code] ....
Using MS SQLServer 2000
Is there a way to create a query that will return only 1 row within a join for example:
select a,b,c,d
from tbl1
inner join tbl2 on top 1 tbl2.a = tbl1.a <-- return only the top record here...
I dont have too much of a problem with joins... however I was wondering if there was a mechanism to just specify what would be the top most record from a join clause.
thanks
Kevin
have the following code for ONE Inner Join, but I want to add another join for another Table and Fields.... can you help me with the syntax:
SELECT DISTINCT
I cant find the problem with this query for the life in me
SELECT ForumTopic.*, websiteinfo.shortdomainname AS author, MemberInfo.postcount AS pc, MemberInfo.joined AS jd FROM ForumTopic INNER JOIN websiteinfo ON ForumTopic.domaininfoid=websiteinfo.domaininfoid INNER JOIN websiteinfo websiteinfo2 ON MemberInfo.domaininfoid=websiteinfo2.domaininfoid WHERE ForumTopic.id = 1
the error message is:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "MemberInfo.domaininfoid" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "MemberInfo.postcount" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "MemberInfo.joined" could not be bound.
Can anyone help with this error? Thanks for any responses
Hello,
I need to create a view which links 5 tables as follows:
I have a Header Table which is keyed on Product and Year which I want to join to a Detail Table which is keyed on Product and Year and Week. I want to see all of the rows from each table, which I think is a FULL OUTER JOIN.
I then have three subsidiary tables for Sales, Orders and Deliveries which are all keyed on Product and Year and Week - I want to join each of these tables separately to the Detail table above so that again I see all of the rows from the Detail Table, the Sales Table, the Orders Table and the Deliveries table. For any Product/YearWeek there may or may not be a row on any of the Sales, Order or Deliveries table, but there will not be any rows on these tables which are not on the Detail Table.
Can I do this in the FROM clause andnif so how, or do I need to do a series of separate SELECTs for the Sales, Orders & Deliveries table with UNION clauses.
Best regards
Colin
Application called Filemaker, I'm now trying to use that experience to teach myself SQL (using Microsoft SQL is SQL 2008 r2).My question is can you have multiple self joins.
I have a table which has two columns (for this example)
spell | spellindicator | episode
the spell is not unique e.g. there could be several records with the spell 'A1234' the spellindicator column will contain a number 1 or 2. the data could look like
this:
spell-------spellindicator---------episode
A1234-----------1-----------------2
B5674-----------1-----------------1
C8739-----------2-----------------3
A1234-----------1-----------------1
B5674-----------2-----------------1
A123------------2-----------------2
A1234-----------1-----------------3
I want to create two left self joins:
1) to count the records that have the same order number with the spellindicator being 1.
2) to find the max and min episode
For 1) I've created two keys:
pk_indicator = spell +':1'
fk_indicator = spell + ':' + spellindicator
example data:
pk_indicator------fk_indicator
A1234:1-----------A1234:1
B5674:1-----------B5674:1
C8739:1-----------C8739:2
A1234:1-----------A1234:1
B5674:1----------- B5674:2
A1234:1-----------A1234:2
A1234:1-----------A1234:1
For2) The join is on the spell
Part 1) works fine on its own
Part 2) works fine on its own
But put both joins in the same statement and the count function no longer works correctly. I realise that there is an issue with the self joins but I thought it would be possible to have multiple self joins.
use leis
go
select l1.spell, l1.spellindicator, l1.episode, l1.pk_Indicator, l1.fk_indicator
,maxepisode = MAX(e2.episode)
,minepisode = MIN(e2.episode)
[code]....
I have a customer table which has customer billing addresses; an orders table which has payment and shipping method info; and an items table which has line items for each order. The customer table has a custnum index field. The orders table has a custnum and orderid field. And the items table has an orderid field.
I'd like to be able to retrieve, in one query, the customer's billing address, all of their orders, with each of their order's line items.
What would the query look like in order to accomplish this?
Thanks in advance.
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
ID Pat_ID
1 A
2 A
3 A
4 A
5 A
6 B
7 B
8 B
9 C
10 D
11 D
I want output for the above as:
Pat_ID ID1 ID2 ID3
A 1 2 3
A 4 5 null
B 6 7 8
C 9 null null
D 10 11 null
Please help me. Thanks!
Here is what I am currently doing:
********************************************
SELECT Hours.Hours, Hours.Comments
FROM Hours INNER JOIN Employee
ON Employee.UserID = Hours.UserID
INNER JOIN Task
ON Hours.TaskID = Task.TaskID
INNER JOIN Project
ON Hours.ProjID = Project.ProjID
WHERE Hours.Date <= EndDate
AND Hours.Date >= StartDate
AND Hours.Date <= EndDate;
********************************************
Am I doing something wrong here?
Any help would be greatly appreciated!
I'm trying, with little success, to achieve something that should be quite easy (I think!) and any advice would be appreciated.
I have a leagues table structured so:
LeagueID | Name | Player1 | Player 2 ... Player6
and the data in the player columns is a userid from the users table and I'm trying to display the Leagues but with the player names rather than player IDs.
I'm working along the lines of
Code:
select
u1.displayname as Player1,
u2.displayname as Player2
from DCMLeagues as L
inner join Users as u1 on L.player1 = u1.userid
inner join Users as u2 on L.player2 = u2.userid
but with little success so far. Any thoughts would be appreciated! Thanks very much in advance.
-- Chris
I'm putting together the site for a local darts league, essentially each of the teams completes an 'e-scorecard' for that particular game. This is then stored in a table named 'scorecardIndex' :
scxUID(PK)scxTeam1(int)scxTeam2(int)scxTeam1Capt(int)scxTeam2Capt(int)scxDate(nvarchar 50)scxSubmitBy(int)
So each 'index' points off to the UID of the player who was captain, and the UID of of each team.
What I'm struggling with is joining this all together, as I'm joining both scxTeam1Capt and scxTeam2Capt to the same columns in the 'players' table (plFirstName, plSurname)
plUID(PK)plFirstName(nvarchar 50)plSurname(nvarchar 50) plTeamUID(int)plEmail(nvarchar80)plPassword(nvarcharMAX)
And equally joining scxTeam1 & scxTeam2 to the Teams table to pull back the teams actual name.
Using several inner joins I can pull back the entire row for each team, but the columns have the same name..
SELECT *
FROM scorecardIndex AS S
INNER JOIN teams AS T1
ON T1.teamUID = S.scxTeam1
INNER JOIN teams AS T2
ON T2.teamUID = S.scxTeam2
I'm using MS SQL Server 2008 R2.
I've tried to be as detailed as possible without overdoing it.
Could somebody tell me what is the secret of being able to write a SELECT statement having mulitple LEFT or RIGHT joins, I seem to get in trouble as soon as I add the second LEFT join, as I am obviously doing it wrong.
These are my tables, would somebody mind having a go,or explaining what do i need to be aware of in a case like this
emailDetails table ( emailID_PK,emailName,emailText,emailSubject,emailN otificationTypeID)
emailRecipients table (emailID_PK, RecipientID, SentToEmail)
luEmailNotificationTypes look up table (emailNOtificationTypeID_PK, emailNotificationTypeName)
Thanks
Table 1:
id amount
1 100
2 200
3 300
4 400
Table 2:
id amount
1 100
1 100
2 200
3 300
4 null
Table 3:
id amount
1 null
2 200
2 200
3 300
3 200
4 null
id is common for each tables , how can i get output like this:
Collapse | Copy Code
id t1 t2 t3
1 100 200 null
2 200 200 200
3 300 300 500
4 400 null null
I am stuck with this query .
In the below query
with cte1
as (select siteid
,productcategory
,sum(isnull(netsales,0)) as netsales
from dbo.vw_sv_invoicedetail
where invoicedate>=dateadd(dd,-90,getdate())
[Code] ....
I need to add routeid column so that i can finally display sum of sales grouped by routeid in addition to the siteid that i am already displaying.
I have a general question concerning joins. Below is a table scenario:
SELECT *
FROM TABLE_A T0
INNER JOIN TABLE_B T1 ON T1.[Some_Column] = T0.[Some Column]
LEFT JOIN TABLE_C T2 ON T2.[Some_Column] = T0.[Some Column]
Does the above indicate that all records in common between TABLE_A & TABLE_B will be returned, then the records from TABLE_C will be joined to the initial 'result set' (that is the result of joining TABLE_A & TABLE_B), or will TABLE_C simply be joined to TABLE_A regardless of the inner join between TABLE_A & TABLE_B?
Hi I have a requirement where in i haev to convert the SQL from Oracleto the one which will run on the SQL server.in the Oracle Query i am doing multiple joins, between some 13 tables.and some of these joins are inner joins and some are Left outer joins.table1 inner joined with table 2table2 inner join with table3table2 inner join with table4table2 left join with table5table5 left jin with table6table6 left jin with table7table7 left jin with table8table8 left jin with table9Any idea how to achieve this??Tianaren
View 2 Replies View RelatedHi All Database Gurus,I am trying to write code which will produce all the possible validqueries, given tables and join information for tables.Right now i am just trying to construct all the sequential joins.eg. if i have 4 tables A, B, C, D and the join conditions areA Inner join B,B Inner Join C,C Left Outer join Dthen i am constructing joins as :1. A IJ B IJ C LJ D2. B IJ A IJ C LJ D3. B IJ C IJ A LJ D4. B IJ C LJ D IJ A5. C IJ B IJ A LJ D6. C IJ B LJ D IJ A7. C LJ D IJ B IJ AI am not placing any paranthesis to specify the join order. And manyof them are giving me same output.Can anybody tell me how to detect the joins which will give the sameoutput ?here in this case the number of combinations are 7 but for 8 tables iam getting 420 combinations and many of them are same.so please help me to reduce the number of combinations.eagerly waiting for suggetions.Thanking you.Prem.(premratan@hotmail.com)
View 1 Replies View RelatedI'm using Sql 2005 and merge replication.
I want to create a dynamic merge filter on a table but the data I filter on is only reachable by a few extra joins. For example:
I want to filter Table3. Table3 is joined to Table2 which in joined to Table1. Table1 contains the host_name column that I use to filter i.e. host_name = HOSTNAME().
In order to get filtered data in Table3 do I have to also filter Table 2 and Table1, or in my filter clause for Table3 can I say something like: Table3.FKid = Table2.Id and Table2.FKId = Table1.Id and Table1.host_name = HOSTNAME()?
I'm pretty sure I can't use AND in my dynamic filter clause but I just wanted to make sure.
Thanks for your help
Graham
I'm new to SQL 2005 & C# - I'm a MySQL/PHP crossover.
I'm using s Stored Procedure and I'm trying to do multiple joins onto
one table. I have 6 fields in one table that are foreign keys of
another table:
Table1
---------
id
PrimaryCode
SecondaryCode1
SecondaryCode2
SecondaryCode3
SecondaryCode4
SecondaryCode5
Table 2
---------
id
Title
CommCode
The fields in table 1 (except is obviously) hold the id of a row in
Table 2. When displaying data I want to display "Title" -
"CommCode" for each item in Table 1. I got myself started by
searchig on the net and I have a stored procedure. The obvious
problem is that as it goes through the Query only the last value
remains in place - since each value before it is cleared in the
UNION. How can I do this?? Here's my Stored Procedure:
=====================================
ALTER PROCEDURE GetRegistersSpecific
@SearchTxt int
AS
SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
PrimaryCode AS MyID, Title, CommodityCode
FROM registrations
JOIN CommodityCodes ON PrimaryCode = CommodityCodes.id
UNION
SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode1 AS MyID, Title, CommodityCode
FROM registrations
JOIN CommodityCodes ON SecondaryCode1 = CommodityCodes.id
UNION
SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode2 AS MyID, Title, CommodityCode
FROM registrations
JOIN CommodityCodes ON SecondaryCode2 = CommodityCodes.id
UNION
SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode3 AS MyID, Title, CommodityCode
FROM registrations
JOIN CommodityCodes ON SecondaryCode3 = CommodityCodes.id
UNION
SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode4 AS MyID, Title, CommodityCode
FROM registrations
JOIN CommodityCodes ON SecondaryCode4 = CommodityCodes.id
UNION
SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode5 AS MyID, Title, CommodityCode
FROM registrations
JOIN CommodityCodes ON SecondaryCode5 = CommodityCodes.id
WHERE registrations.ID = @SearchTxt
=====================================
Thanks
Hi,I am written a store procedure that would access four tables and grab appropriate fields.. I am using JOIN functionality because it can have multiple rows. ( The goal is: )Step 1: User can search by ID or MEMBER_ID or both .. grab all the data from mainTable based on the search. WORKS.Step 2: TABLE 2 (userTable table) get EMAIL for each record that was grabbed.. based on the ID. WORKS.Step 3: TABLE 3 and TABLE 4.. I am having some problems combing into the query.. how to add multiple JOINS… Is it safe? Please see below what data needs to be combined into the query.--Code works for Step 1 and 2.declare @ID varchar(20), @MEMBER_ID varchar(20) set @ID= null --testing data.. set @MEMBER_ID ='15552' –testing data.. Select MainTable.REFNO,MainTable.ID,mainTable.MEMBER_ID,userTable.EMAILFROM mainTableLEFT JOIN userTableON mainTable.ID = userTable.IDWhere (mainTable.ID = @ID OR @ID IS NULL) and(mainTable.MEMBER_ID = @MEMBER_ID OR @MEMBER_ID IS NULL)TABLE 3: (works by itself)SELECT SR.COMPANY, SR.LOCATION_NOFROM SI INNER JOIN SR ON SI.SR_ID = SR.SR_IDWHERE SI.ID = MainTable.ID)ORDER BY SR.DATE_RECEIVED DESCTABLE 4: (works by itself)I will be retrieving LOCATION_NO from SR table and comparing the value to the below query: for each record that was found in the mainTable.select LOCATION_NAME from locationwhere LOCATION_NO= SR.LOCATION_NO Please help me solve this.. Thank you
View 5 Replies View RelatedI am trying to do a query like this...
UPDATE g
SET g.GroupID = gp.GroupID, g.Contact1 = members.FirstName, g.BusPhone1 = members.BusPhone, g.HomePhone1 = members.HomePhone, g.Internet1 = members.Email
FROM statelst AS g INNER JOIN
grpcon AS gp ON g.GroupID = gp.GroupID INNER JOIN
members ON gp.MemberID = members.MemberID CROSS JOIN
I have my table statelst that I want to update certain columns from the values returned by a select on the grpcon table joined to the members table.I am getting an error "Incorrect syntex near 'JOIN'.
Hello Everyone,
I have a quite unusual problem, and I have hard time finding the answer.
I have a table with Locations - lets say that it has just ID, and Name,
and a Transport table containing the ID, ArrivalLocationID and DepartureLocationID.
Now - when I select the Transport table I want to get names of the Arrival and Departure locations from th other table.
If it was a single link I woul do an INNER JOIN like:
SELECT
Transport.*,
Locations.Name AS ArrivalLocation
FROM TransportProductOperationPeriods
INNER JOIN Locations ON
Transport.ArrivalLocation = Locations.ID
But I want to do a double INNER JOIN between two same table. And here I get a problem - how to do it? Something like:
SELECT
TransportProductOperationPeriods.*,
Locations.Name AS LArrivalLocation,
Locations.Name AS LDepartureLocation,
Locations.ID AS LArrivalLocationID,
Locations.ID AS LDepartureLocationID
FROM TransportProductOperationPeriods
INNER JOIN Locations ON
TransportProductOperationPeriods.ArrivalLocation = LArrivalLocationID
INNER JOIN Locations ON
TransportProductOperationPeriods.DepartureLocation = LDepartureLocationID
but obviously that does not work ;)
Thanks in advance,
Joseph
I need to do multiple left outer join to return search profiles that could contain NULL in them that could also be foreign keys. I bolded the three IDs that could be NULL or have a foreign key for a value. An example with my code would be great I've tried decyphering the many employee and company examples on the web but I haven't figured it out yet. Right now I only get profiles that have foreign key values and it misses the rest in the search. So NULL MakeID or ModelID no result on that item my SQL statement below. Using SQL Server 2005. Pretty new this to SQL and databases but so far this has been the only trying part.
Thanks
String dbsql = "SELECT a.EquipmentID " +
" , a.SerialNo " +
" , b.Category " +
" , c.Subcategory " +
" , d.Make " +
" , e.Model " +
" , f.Status " +
" FROM tblEquipInfo a " +
" , tblEquipCat b " +
" , tblEquipSubcat c " +
" , tblEquipMake d " +
" , tblEquipModel e " +
" , tblStatus f " +
" WHERE b.Category = '" + val + "' " +
" AND a.CategoryID = b.CategoryID " +
" AND a.SubcategoryID = c.SubcategoryID " +
" AND a.MakeID = d.MakeID " +
" AND a.ModelID = e.ModelID " +
" AND a.StatusID = f.StatusID";
Here are the tables I am dealing with:
Tables
license TABLE
---------------
license_id int PK
vendor_id int FK
po_id int FK Nulls
license_type_id int FK
lic_user_id int FK Nulls
location_id int FK Nulls
lic_start_date smalldatetime Nulls
days_allowed int Nulls
serial_num varchar(50) Nulls
activation_key varchar(50) Nulls
max_users int Nulls
comments varchar(1000) Nulls
software TABLE
------------------
software_id int PK
os_id int
software_name varchar(150)
comments varchar(1000) Nulls
software_license TABLE
--------------------------
license_id int PK , FK
software_id int PK , FK
comments varchar(1000) Nulls
dept_license TABLE
---------------------
dept_id int PK , FK
license_id int PK , FK
comments varchar(1000) Nulls
purch_order TABLE
---------------------
po_id int PK
po_number varchar(50)
po_date smalldatetime
comments varchar(1000) Nulls
dept_purch_order TABLE
----------------------------
po_id int PK , FK
dept_id int PK , FK
comments varchar(1000) Nulls
I am using the following stored proceedure to try to create a useful report from those tables:
Code Block
ALTER PROCEDURE [dbo].[ListDepartmentLicenses]
@Department int,
@Software int
AS
BEGIN
SET NOCOUNT ON;
SELECT dept_license.license_id,
license.lic_start_date,
department.dept_name,
purch_order.po_number,
software.software_name
FROM dept_license
JOIN license
ON dept_license.license_id = license.license_id
JOIN department
ON dept_license.dept_id = department.dept_id
JOIN dept_purch_order
ON dept_purch_order.dept_id = dept_license.dept_id
JOIN purch_order
ON dept_purch_order.po_id = purch_order.po_id
JOIN software_license
ON dept_license.license_id = software_license.license_id
JOIN software
ON software_license.software_id = software.software_id
WHERE (department.dept_id = @Department or @Department Is Null) and
(software.software_id = @Software or @Software Is Null)
ORDER BY license.lic_start_date
END
The sproc compiles fine but I get no rows returned.
I have a database of news articles and i have a stored procedure that basically pulls one from the database based on an ID number. The author (Press Contact) and publication are stored as just ID numbers and pulled in via JOINs.
SELECT Articles.date_published, Articles.headline, Publication.press_contact,
Publication.pub_name, Articles.body
FROM Articles
LEFT OUTER JOIN
PressContact ON PressContact.press_id = Articles.press_id
LEFT OUTER JOIN
Publication ON Publication.publication_id = Articles.publication_id
WHERE (Articles.id = @ID)
Everything works great in this setup. However, we've recently added a press_id2 field to the articles table to be able to store a 2nd press contact. So now I need my stored procedure to pull out both press contact names and I'm not sure the best way to do that.
I tried to JOIN the PressContact table a 2nd time on PressContact.press_id = Articles.press_id2 but that didn't seem to work.
Can anyone give me any suggestions?
Thanks in advance.
Hi, I am trying to write a query that gets the percentage of students in specific racial groups in specific schools. Some ethnicity values of students are null so I have to use left joins. My query is below, when I run it I get the error "join expression not supported", I've tried a couple different ways of doing it but I always get that error or "syntax error in from clause". Can anybody help me with formatting multiple and nested left joins in general?
Thanks in advance.
drop table percentMinorities;
create view percentMinorities as
select s1.schoolid,
round(count(s2.studentid)/count(s1.studentid),2) as percentWhite,
round(count(s3.studentid)/count(s1.studentid),2) as percentBlack,
round(count(s4.studentid)/count(s1.studentid),2) as percentHispanic,
round(count(s5.studentid)/count(s1.studentid),2) as percentAsian
from
students as s1 left join
(students as s2 left join
(students as s3 left join
(students as s4 left join students as s5
on s4.studentid is not null and s5.ethnicity = 'A')
on s3.studentid is not null and s4.ethnicity = 'H')
on s2.studentid is not null and s3.ethnicity = 'N')
on s1.studentid is not null and s1.ethnicity = 'O'
group by s1.schoolid
I have 3 table as below:
DSF
dsf_id
company_name
incorporation_date
secretary_name
ShareholdersDetails
shareholder_id
dsf_id
shareholder_name
DirectorsDetails
directors_id
dsf_id
directors_name
one company can have multiple shareholders and directors records.
i create a search query where users might search by company name, secretary name , shareholder name or directors name.
My select query is like below:
Code:
SELECT dsf.dsf_id, dsf.company_name,
dsf.incorporation_date, dsf.secretary_name,
s.shareholders_name, d.directors_name
FROM tbl_dsf dsf
LEFT OUTER JOIN tbl_directors d on dsf.dsf_id = d.dsf_id
LEFT OUTER JOIN tbl_shareholders s on dsf.dsf_id = s.dsf_id
[WHERE CONDITION]
The result for above query would be like:
Code:
abc | 1/2/1999 | william | marry | donna
abc | 1/2/1999 | william | jenna | donna
abc | 1/2/1999 | william | jolly | donna
abc | 1/2/1999 | william | marry | dolly
abc | 1/2/1999 | william | jenna | dolly
abc | 1/2/1999 | william | jolly | dolly
Is it possible to achive result as below:
Code:
abc | 1/2/1999 | william | marry,jenna,jolly | donna,dolly
Thanks